<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns='http://www.w3.org/1999/xhtml' xml:lang='en'>
<head>
  <meta content="text/html; charset=ISO-8859-1"  http-equiv="content-type" />
  <link rel="stylesheet" type="text/css" href="style.css" />
  <title>SOCI - beyond standard SQL</title>
</head>

<body>
<p class="banner">SOCI - The C++ Database Access Library</p>

<h2>Beyond standard SQL</h2>

<p>Sometimes the standard SQL is not enough and database-specific syntax needs
to be used. When possible and practical, SOCI provides wrappers hiding the
differences between the backends and this section describes these wrappers.
And if this is still not enough, you can use the backend-specific methods
directly as described below.</p>

<h3>Getting the number of rows affected by an operation</h3>

<p>It can be useful to know how many rows were affected by the last SQL
statement, most often when using <tt>INSERT</tt>, <tt>UPDATE</tt> or
<tt>DELETE</tt>. SOCI provides <code>statement::get_affected_rows()</code>
method allowing to do this:</p>

<pre class="example">
statement st = (sql.prepare &lt;&lt; "update some_table ...");
st.execute(true);

if ( !st.get_affected_rows() )
{
    ... investigate why no rows were modified ...
}
</pre>

<div class="note">
<p><span class="note">Portability note:</span></p>
This method is currently not supported by the Oracle backend. It is however
supported when using Oracle database via ODBC backend.</p>
</div>


<h3 id="sequences">Working with sequences</h3>

<p>It is common to have auto-incrementing database fields or fields whose
value come from a sequence. In the latter case you need to retrieve the value
of the field for a new row before inserting it into the database. In the
former case, this is unnecessary but you may still want to know the value
generated by the database, e.g. to use it as a foreign key in another table.
So it would be useful to have a way to obtain the value of such a field.
But, of course, to make life of database programmers more interesting,
different products usually support either autoincrement fields or sequences
but not both -- and they use different syntaxes for them, too. SOCI tries to
help to deal with this unfortunate situation by providing two functions:
<code>session::get_next_sequence_value()</code> and
<code>session::get_last_insert_id</code>.</p>

<p>If you know which kind of database you use, you may use only one of them:
when working with sequences, the first one allows to generate the next value
in a sequence and when working with autoincrement fields, the second one
retrieves the last value generated for such a field for the given table.</p>

<p>However if you use multiple SOCI backends or even just a single ODBC
backend but support connecting to databases of different types, you actually
must use both of them in the following way to insert a row:</p>

<pre class="example">
long id;
statement st;
if ( sql.get_next_sequence_value("table_sequence", id) )
{
    st &lt;&lt; "insert into table(id, f1, f2) values(:id, :f1, :f2)",
        use(id), use(f1), use(f2);
}
else
{
    // We're not using sequences, so don't specify the value,
    // it will be automatically generated by the database on insert.
    st &lt;&lt; "insert into table(f1, f2) value(:f1, :f2)",
        use(f1), use(f2);

    // If the ID used for the above row is needed later, get it:
    if ( !sql.get_last_insert_id("table", id) )
        ... unexpected error, handle appropriately ...
}
</pre>

<div class="note">
<p><span class="note">Portability note:</span></p>
These methods are currently only implemented in Firebird and ODBC backends.</p>
</div>


<h3>Beyond SOCI API</h3>

<p>As the original name of the library (Simple Oracle Call Interface)
clearly stated, SOCI is intended to be a <i>simple</i> library, targeting the
majority of needs in regular C++ application. We do not claim that
<i>everything</i> can be done with SOCI and it was never the intent of the
library. What is important, though, is that the simplicity of the
library does <i>not</i> prevent the
client applications from reaching into the low-level specifics of each
database backend in order to achieve special configuration or
performance goals.</p>

<p>Most of the SOCI classes have the <code>getBackEnd</code> method,
which
returns the pointer to the actual backend object that implements the
given functionality. The knowledge of the actual backend allows the
client application to get access to all low-level details that are
involved.</p>

<pre class="example">
blob b(sql);

oracle_session_back_end * sessionBackEnd = static_cast&lt;oracle_session_back_end *&gt;(sql.get_back_end());
oracle_blob_back_end * blobBackEnd = static_cast&lt;oracle_blob_back_end *&gt;(b.get_back_end());

OCILobDisableBuffering(sessionBackEnd-&gt;svchp_, sessionBackEnd-&gt;errhp_, blobBackEnd-&gt;lobp_);
</pre>

<p>The above code creates the <code>blob</code> object and uses two calls
to the <code>get_back_end</code> function (on both the <code>session</code>
and the <code>blob</code> objects) to get access to the actual backend
objects. Assuming that it is the <code>"oracle"</code> backend which
is in use, the downcasts allow to access all relevant low-level handles
and use them in the call
to the <code>OCILobDisableBuffering</code> function. This way, the
BLOB handle was configured in a way that the SOCI library alone would
not allow.</p>

<pre class="example">
rowid rid(sql); // sql is a session object
sql &lt;&lt; "select oid from mytable where id = 7", into(rid);

postgresql_rowid_back_end * rbe = static_cast&lt;postgresql_rowid_back_end *&gt;(rid.get_back_end());

unsigned long oid = rbe-&gt;value_;
</pre>

<p>The above example retrieves the <code>rowid</code> ("something" that
identifies the
row in the table) from the table and uses the <code>get_back_end</code>
function to
extract the actual object that implements this functionality. Assuming
that it is the <code>"postgresql"</code> backend which is in use, the
downcast is
performed to use the <code>postgresql_rowid_back_end</code> interface to
get the actual
OID value that is a physical, low-level implementation of row
identifier on PostgreSQL databases.</p>

<p>In order for any of the above to compile, you have to explicitly <code>#include</code>
the appropriate backend's header file.</p>

<p>Please see the header file related to the given backend to learn what
low-level handles and descriptors are available.</p>

<table class="foot-links" border="0" cellpadding="2" cellspacing="2">
  <tr>
    <td class="foot-link-left">
      <a href="statements.html">Previous (Interfaces)</a>
    </td>
    <td class="foot-link-right">
      <a href="reference.html">Next (Client reference)</a>
    </td>
  </tr>
</table>

<p class="copyright">Copyright &copy; 2012 Vadim Zeitlin</p>
<p class="copyright">Copyright &copy; 2004-2006 Maciej Sobczak, Stephen Hutton</p>
</body>
</html>
